package com.sl.au.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.AfterSaleTicket;
import com.sl.au.entity.Contract;

public interface AfterSaleTicketRepository extends JpaRepository<AfterSaleTicket, String> {

	/**
	 * 根据key得到所有数据（所有）
	 * 
	 * @param key
	 *            查询条件
	 * @param pageable
	 * @return Page<AfterSaleTicket>
	 */
	@Query(" select a from AfterSaleTicket a where (a.isSubmit !=0 or '系统管理员'=?2) "
			+ "and a.isSubmit like '%'+?3+'%' "
			+ "and a.afterSaleType like '%'+?4+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' " 
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> findAllTicketByKey(String key, String roleName,String isSubmit, String afterSaleType, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket a where (a.isSubmit !=0 or '系统管理员'=?2) "
			+ "and a.isSubmit like '%'+?3+'%' "
			+ "and a.afterSaleType like '%'+?4+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' " 
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.linkMan like '%'+?1+'%') ")
	long getAllCount(String key, String roleName,String isSubmit, String afterSaleType);

	/**
	 * 根据key得到本部门服务申请（本部门），（or提交人为本人的）
	 * 
	 * @param key
	 *            查询条件
	 * @param superior
	 *            部门
	 * @return Page<AfterSaleTicket>
	 */
	@Query("select a from AfterSaleTicket a where ((a.user.saleArea.superior=?2 and a.isSubmit>1)"
			+ "or (a.user.id=?3) or (a.submiter.id=?3))"
			+ "and a.isSubmit like '%'+?4+'%' "
			+ "and a.afterSaleType like '%'+?5+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.proposer like '%'+?1+'%' or a.linkMan like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%')")
	Page<AfterSaleTicket> findSuperiorPage(String key, String superior, String userId,String isSubmit, String afterSaleType, Pageable pageable);

	@Query("select count(*) from AfterSaleTicket a where ((a.user.saleArea.superior=?2 and a.isSubmit>1)"
			+ "or (a.user.id=?3) or (a.submiter.id=?3))"
			+ "and a.isSubmit like '%'+?4+'%' "
			+ "and a.afterSaleType like '%'+?5+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.proposer like '%'+?1+'%' or a.linkMan like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%')")
	long getSuperiorCount(String key, String superior, String userId,String isSubmit, String afterSaleType);

	/**
	 * 根据key得到本片区数据（本片区），（or提交人为本人的）
	 * 
	 * @param key
	 *            查询条件
	 * @param areaId
	 *            片区Id saleArea Id
	 * @return Page<AfterSaleTicket>
	 */

	@Query(" select a from AfterSaleTicket a where ((a.user.saleArea.id = ?2 "
			+ "and a.isSubmit>1) or ((a.user.id=?3) or (a.submiter.id=?3))) "
			+ "and a.isSubmit like '%'+?4+'%' "
			+ "and a.afterSaleType like '%'+?5+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' or a.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> finAreaPage(String key, String areaId, String userId,String isSubmit, String afterSaleType, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket a where ((a.user.saleArea.id = ?2 "
			+ "and a.isSubmit>1) or ((a.user.id=?3) or (a.submiter.id=?3))) "
			+ "and a.isSubmit like '%'+?4+'%' "
			+ "and a.afterSaleType like '%'+?5+'%' "
			+ "and (a.unitName like '%'+?1+'%' "
			+ "or a.contractCode like '%'+?1+'%' or a.manageCode like '%'+?1+'%' " 
			+ "or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' or a.linkMan like '%'+?1+'%') ")
	long getAreaCount(String key, String areaId, String userId,String isSubmit, String afterSaleType);

	/**
	 * 售后服务单申请主界面数据（本人）或（提交人为本人的）
	 * 
	 * @param key
	 *            查询条件
	 * @param pageable
	 * @return Page<AfterSaleTicket>
	 */
	@Query(" select t from AfterSaleTicket t where (t.user.id = ?2 or t.submiter.id=?2) "
			+ "and t.afterSaleType like '%'+?4+'%' "
			+ "and t.isSubmit like '%'+?3+'%' and (t.unitName like '%'+?1+'%' "
			+ "or t.contractCode like '%'+?1+'%' or t.manageCode like '%'+?1+'%' " 
			+ "or t.submiter.empLogin.userName like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> findTicketBykey(String key, String userId,String isSubmit, String afterSaleType, Pageable pageable);

	/**
	 * 售后服务单主界面数据数量（本人）
	 * 
	 * @param key
	 * @return long
	 */
	@Query(" select count(*) from AfterSaleTicket t where (t.user.id = ?2 or t.submiter.id=?2) "
			+ "and t.afterSaleType like '%'+?4+'%' "
			+ "and t.isSubmit like '%'+?3+'%' and (t.unitName like '%'+?1+'%' "
			+ "or t.contractCode like '%'+?1+'%' or t.manageCode like '%'+?1+'%' " 
			+ "or t.submiter.empLogin.userName like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	long getCountBykey(String key, String userId,String isSubmit, String afterSaleType);

	/*
	 * 获得（本人被退回的服务申请）
	 */
	@Query(" select t from AfterSaleTicket t where  t.user.id = ?2 and t.isSubmit=1 and (t.unitName like '%'+?1+'%' "
			+ "or t.afterSaleType like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> getRejectBykey(String key, String userId, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket t where t.user.id = ?2 and t.isSubmit=1 and (t.unitName like '%'+?1+'%' "
			+ "or t.afterSaleType like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	long getRejectCountBykey(String key, String userId);

	/*
	 * 获得本人需提交的服务申请
	 */
	@Query(" select t from AfterSaleTicket t where  t.submiter.id=?2 and t.isSubmit=0 and (t.unitName like '%'+?1+'%' "
			+ "or t.afterSaleType like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> getSubmitBykey(String key, String userId, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket t where t.submiter.id=?2 and t.isSubmit=0 and (t.unitName like '%'+?1+'%' "
			+ "or t.afterSaleType like '%'+?1+'%' or t.proposer like '%'+?1+'%' " + "or t.linkMan like '%'+?1+'%') ")
	long getSubmitCountBykey(String key, String userId);

	/**
	 * 售后内勤主界面数据
	 * 
	 * @param key
	 * @param pageable
	 * @return Page<AfterSaleTicket>
	 */
	@Query(" select a from AfterSaleTicket a where (a.isSubmit>1) "
			+ "and a.isSubmit like '%'+?2+'%'"
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.submiter.empLogin.userName like '%'+?1+'%' or a.proposer like '%'+?1+'%' "
			+ "or a.ticketState like '%'+?1+'%' or a.manageCode like '%'+?1+'%' "
			+ "or a.deviceName like '%'+?1+'%' or a.deviceCode like '%'+?1+'%' "
			+ "or a.superior like '%'+?1+'%' or a.contractCode like '%'+?1+'%' "
			+ "or a.createTime like '%'+?1+'%' or a.serviceTime like '%'+?1+'%' "
			+ "or a.deviceNumber like '%'+?1+'%' or a.questionDetail like '%'+?1+'%' "
			+ " or a.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> findInHouseTicket(String key,String isSubmit, Pageable pageable);

	/**
	 * 售后内勤主界面数据数量
	 * 
	 * @param key
	 * @return long
	 */
	@Query(" select count(*) from AfterSaleTicket a where (a.isSubmit>1) "
			+ "and a.isSubmit like '%'+?2+'%' "
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.submiter.empLogin.userName like '%'+?1+'%' or a.proposer like '%'+?1+'%' "
			+ "or a.ticketState like '%'+?1+'%' or a.manageCode like '%'+?1+'%' "
			+ "or a.deviceName like '%'+?1+'%' or a.deviceCode like '%'+?1+'%' "
			+ "or a.superior like '%'+?1+'%' or a.contractCode like '%'+?1+'%' "
			+ "or a.createTime like '%'+?1+'%' or a.serviceTime like '%'+?1+'%' "
			+ "or a.deviceNumber like '%'+?1+'%' or a.questionDetail like '%'+?1+'%' "
			+ " or a.linkMan like '%'+?1+'%') ")
	long getInHouseCount(String key,String isSubmit);

	/**
	 * 根据查询条件查询项目合同数据,通过
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select c from Contract c where c.supplierCode like '%'+?1+'%' or c.demanderCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%' ")
	Page<Contract> getContracts(String key, Pageable pageable);

	/**
	 * 根据查询条件查询项目合同数据条数
	 * 
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select count(*) from Contract c where c.state='通过' and (c.supplierCode like '%'+?1+'%' or c.demanderCode like '%'+?1+'%' or c.projectName like '%'+?1+'%' "
			+ "or c.orderUnit like '%'+?1+'%') ")
	long getContractCount(String key);

	/*
	 * 获得本人的待回访和完成的服务单
	 */
	@Query(" select a from AfterSaleTicket a where (a.isSubmit like '%'+?3+'%') " + "and a.user.id=?2 "
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.manageCode like '%'+?1+'%'or a.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> findMyTicket(String key, String userId, String isSubmit, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket a where (a.isSubmit like '%'+?3+'%') " + "and a.user.id=?2 "
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.manageCode like '%'+?1+'%'or a.linkMan like '%'+?1+'%') ")
	long getMyTicketCount(String key, String userId, String isSubmit);

	/*
	 * 获得本人的待回访服务单
	 */
	@Query(" select a from AfterSaleTicket a where (a.isSubmit=5) " + "and a.user.id=?2 "
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.manageCode like '%'+?1+'%'or a.linkMan like '%'+?1+'%') ")
	Page<AfterSaleTicket> findMyUndoTicket(String key, String userId, Pageable pageable);

	@Query(" select count(*) from AfterSaleTicket a where (a.isSubmit=5) " + "and a.user.id=?2 "
			+ "and (a.unitName like '%'+?1+'%' or a.afterSaleType like '%'+?1+'%' "
			+ "or a.proposer like '%'+?1+'%' or a.manageCode like '%'+?1+'%'or a.linkMan like '%'+?1+'%') ")
	long getMyTicketUndoCount(String key, String userId);

	/**根据单位名称查找已提交的
	 * @param key
	 * @param unitName
	 * @param pageable
	 * @return
	 */
	@Query("select a from AfterSaleTicket a where (a.afterSaleType like '%'+?1+'%' or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' or a.linkMan like '%'+?1+'%') and ( a.contractId in ( select c.contractId from ContractInfo c where c.customerId=?2)) and  (a.isSubmit!=0 and a.isSubmit!=1)")
	Page<AfterSaleTicket>findCustomerAfterSaleInfo(String key,String customerId,Pageable pageable);
	@Query("select count(*) from AfterSaleTicket a where (a.afterSaleType like '%'+?1+'%' or a.proposer like '%'+?1+'%' or a.submiter.empLogin.userName like '%'+?1+'%' or a.linkMan like '%'+?1+'%') and ( a.contractId in ( select c.contractId from ContractInfo c where c.customerId=?2)) and  (a.isSubmit!=0 and a.isSubmit!=1)")
	long getCount(String key,String customerId);
}
